const express = require('express');
const { avatar, logo, picture, bankcard, file, customUpload } = require('../utils/upload');
const { validate, validators } = require('../utils/validate');
const { db } = require('../utils/database');
const {
	makePassword,
	checkPassword,
	dataDiff,
	dataFormat,
	getBaiduToken,
	baiduIdcard,
	comparePersonArrays,
	compareMatterTagArrays
} = require('../utils');
const { setToken, getToken } = require('../utils/jwt');
const { ManagerAuth, Authentication } = require('../utils/middles');
const FormData = require('form-data');

//不需要验证登入部分
const notAuthRouter = express.Router();
notAuthRouter
	/**
	 *管理者注册
	 */
	.post(
		'/register',
		avatar.single('avatar'),
		validate([validators.mUsername, validators.password, validators.confirmPassword]),
		async (req, res) => {
			const { username, password, role = 'guest' } = req.body;
			let manager = await db.findOne('select * from managers where username = ?', [username]);
			if (manager) {
				return res.send({ success: false, message: '该用户名已经存在' });
			}
			let avatar = req.file?.filename ? '/avatars/' + req.file?.filename : '/avatars/current.png';
			// 插入用户信息
			const [results] = await db.query(
				'insert into managers (avatar,username,nickname,password,role) values (?,?,?,?,?)',
				[avatar, username, '新用户', makePassword(password), role]
			);
			// 查询刚刚插入的用户信息
			manager = await db.findOne('select * from managers where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '恭喜，注册成功！',
				manager
			});
		}
	)
	/**
	 * 检查用户名
	 */
	.get('/register/check', validate([validators.qUsername]), async (req, res) => {
		const { username } = req.query;
		const exist = await db.findOne('select * from managers where username = ?', [username]);
		if (exist) {
			return res.send({ success: false, message: '' });
		}
		res.send({ success: true, message: '' });
	})
	/**
	 * 用户登录
	 */
	.post('/login', validate([validators.mUsername, validators.password]), async (req, res) => {
		const { username, password } = req.body;
		// 根据用户名查询用户信息
		let user = await db.findOne('select * from managers where username = ?', [username]);
		if (user && user.status) {
			return res.send({
				success: false,
				message: '用户账号封禁，请联系管理员！'
			});
		}
		// 判断密码是否输入正确
		if (user && checkPassword(password, user.password)) {
			// 查询更改用户活跃状态
			let day = dataDiff(user.last_login, 'days'),
				bool = null;
			if (day != null && day < 7) {
				bool = true;
			} else {
				bool = false;
			}
			// 改变用户活跃状态
			await db.query('update managers set active = ?, last_login = CURRENT_TIMESTAMP where id = ?', [bool, user.id]);
			user = await db.findOne('select * from managers where id = ?', [user.id]);
			user.created_at = dataFormat(user.created_at, 'YYYY-MM-DD kk:mm:ss');
			user.last_login =
				dataFormat(user.last_login, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
					? user.last_login
					: dataFormat(user.last_login, 'YYYY-MM-DD kk:mm:ss');
			// 在会话 （session）中保存用户信息
			// req.session.userId = user.id
			return setToken({ userId: user.id }).then((token) => {
				// 响应客户端
				res.send({
					success: true,
					message: '欢迎回来！',
					user,
					token: token
				});
			});
		}
		res.send({ success: false, message: '用户名或密码错误' });
	});

//需要验证登入部分
const router = express.Router();
router.use(ManagerAuth);

router
	// 获取当前用户信息
	.get('/userinfo', async (req, res) => {
		const { id } = req.query;
		if (id) {
			let manager = await db.findOne('select * from managers where id = ?', [id]);
			if (!manager || manager.status === 1) {
				return res.send({
					success: false,
					message: '账号不存在或异常!'
				});
			} else {
				manager.created_at = dataFormat(manager.created_at, 'YYYY-MM-DD kk:mm:ss');
				return res.send({
					success: true,
					manager
				});
			}
		}
		req.user.created_at = dataFormat(req.user.created_at, 'YYYY-MM-DD kk:mm:ss');
		res.send({
			success: true,
			manager: req.user
		});
	})
	/**
	 * 用户注销
	 */
	.get('/logout', (req, res) => {
		// 清除 session 信息 （不使用）
		// req.session.destroy();
		res.send({
			success: true,
			message: '用户注销成功'
		});
	})
	/**
	 * 获取所有管理员用户
	 */
	.get('/get_all_user', validate([validators.limit, validators.offset]), async (req, res) => {
		const { username = '', role = '', limit = '10', offset = '0' } = req.query;
		let wheres = ['where status = 0'];
		if (username !== '') {
			wheres.push(`username like '%${username}%'`);
		} else {
			wheres.push(`username is not null`);
		}
		if (role !== '') {
			wheres.push(`role = '${role}'`);
		} else {
			wheres.push(`username is not null`);
		}
		const [results] = await db.query(`select * from managers ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		const { count } = await db.findOne(`select count(*) as count from managers ${wheres.join(' and ')}`);
		res.send({
			success: true,
			managers: results,
			query: {
				count: count
			}
		});
	})
	/**
	 * 禁用管理员
	 */
	.put('/banned/:tid', Authentication('guest'), validate([validators.tid]), async (req, res) => {
		let manager = await db.findOne('select * from managers where id = ?', [req.params?.tid]);
		if (!manager) {
			return res.send({
				success: false,
				message: '账号不存在!'
			});
		} else {
			if (manager.role === 'super') {
				return res.send({
					success: false,
					message: '超级管理员不能被禁用!'
				});
			} else if (req.user.id === req.params?.tid) {
				return res.send({
					success: false,
					message: '自身不能禁用自身账号!'
				});
			}
			await db.query('update managers set status = ? where id = ?', [manager.status ? 0 : 1, req.params?.tid]);
			manager = await db.findOne('select * from managers where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: manager.status ? '禁用成功' : '解禁成功',
				manager
			});
		}
	})
	/**
	 * 修改用户基本信息
	 */
	.put(
		'/profile/:tid',
		validate([validators.tid]),
		avatar.single('avatar'),
		validate([
			validators.nickname,
			validators.job,
			validators.level,
			validators.email,
			validators.phone,
			validators.qq,
			validators.wechat
		]),
		async (req, res) => {
			let manager = await db.findOne('select * from managers where id = ?', [req.params?.tid]);
			if (!manager || manager.status === 1) {
				return res.send({
					success: false,
					message: '账号不存在或异常!'
				});
			}
			let currentUser = req.user,
				targetUser = manager;
			if (currentUser.id !== targetUser.id) {
				if (targetUser.role === 'super') {
					return res.send({
						success: false,
						message: '超级管理员只能被自己修改！'
					});
				} else if (targetUser.role === 'admin' && currentUser.role !== 'super') {
					return res.send({
						success: false,
						message: '管理员只能被自己修改！'
					});
				} else if (targetUser.role === 'guest' && currentUser.role === 'guest') {
					return res.send({
						success: false,
						message: '当前权限不足！'
					});
				}
			}
			const { nickname, job, level, email, phone, qq, wechat } = req.body;
			await db.query(
				'update managers set avatar = ?, nickname = ?, job = ?, level = ?, email = ?, phone = ?, qq = ?, wechat = ? where id = ?',
				[
					req.file?.filename ? '/avatars/' + req.file?.filename : manager.avatar,
					nickname,
					job,
					level,
					email,
					phone,
					qq,
					wechat,
					req.params?.tid
				]
			);
			manager = await db.findOne('select * from managers where id = ?', [req.params?.tid]);
			manager.create_at = dataFormat(manager.create_at, 'YYYY-MM-DD kk:mm:ss');
			manager.last_login =
				dataFormat(manager.last_login, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
					? manager.last_login
					: dataFormat(manager.last_login, 'YYYY-MM-DD kk:mm:ss');
			res.send({
				success: true,
				message: '修改成功',
				manager
			});
		}
	)
	/**
	 * 修改用户名
	 */
	.put('/updateUsername', validate([validators.oldUsername, validators.newUsername]), async (req, res) => {
		const { oldUsername, newUsername } = req.body;
		if (oldUsername !== req.user.username)
			// 根据用户名查询用户信息
			return res.send({ success: false, message: '原用户名错误，请重新输入！' });
		if (oldUsername == newUsername)
			return res.send({ success: false, message: '新用户名与原用户名相同，请重新输入！' });
		let manager = await db.findOne('select * from managers where username = ?', [newUsername]);
		if (manager) return res.send({ success: false, message: '该用户已存在，请重新输入！' });
		await db.query('update managers set username = ? where id = ?', [newUsername, req.user.id]);
		manager = await db.findOne('select * from managers where username = ?', [newUsername]);
		res.send({
			success: true,
			message: '用户名修改成功',
			manager
		});
	})
	/**
	 * 修改用户密码
	 */
	.put(
		'/updatePassword',
		validate([validators.oldPassword, validators.newPassword, validators.againPassword]),
		async (req, res) => {
			const { oldPassword, newPassword } = req.body;
			// 根据用户名查询用户信息
			if (!checkPassword(oldPassword, req.user.password))
				return res.send({ success: false, message: '原密码错误，请重新输入！' });
			await db.query('update managers set password = ? where id = ?', [makePassword(newPassword), req.user.id]);
			if (oldPassword == newPassword) return res.send({ success: false, message: '新密码与原密码相同，请重新输入！' });
			res.send({
				success: true,
				message: '密码修改成功'
			});
		}
	)
	/**
	 * 获取所有项目
	 */
	.get('/get_all_project', validate([validators.limit, validators.offset]), async (req, res) => {
		const {
			title = '',
			project_num = '',
			priority = '',
			is_finished = '',
			is_delete = '',
			limit = '10',
			offset = '0'
		} = req.query;
		let wheres = [`where title like '%${title}%' and project_num like '%${project_num}%'`];
		if (priority != '') {
			wheres.push(`priority = '${priority}'`);
		}
		if (is_finished != '') {
			wheres.push(`is_finished = '${is_finished}'`);
		}
		if (is_delete != '') {
			wheres.push(`is_delete = '${is_delete}'`);
		}
		const [results] = await db.query(`select * from projects ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		const { count } = await db.findOne(`select count(*) as count from projects ${wheres.join(' and ')}`);
		if (results) {
			for (let i of results) {
				i.person_list = [];
				const [person] = await db.query(`select * from projectNeeds where project_id = ${i.id}`);
				for (let item of person) {
					const manager = await db.findOne(`select id,avatar,nickname from managers where id = ${item.manager_id}`);
					i.person_list.push(manager);
				}
			}
		}
		res.send({
			success: true,
			projects: results,
			query: {
				count: count
			}
		});
	})
	/**
	 * 添加项目
	 */
	.post(
		'/add_project',
		picture.single('icon'),
		Authentication('guest'),
		validate([
			validators.project_title,
			validators.project_num,
			validators.project_priority,
			validators.completion_at,
			validators.total_matter,
			validators.finished_matter
		]),
		async (req, res) => {
			const {
				title,
				project_num,
				priority = 'low',
				total_matter = 0,
				finished_matter = 0,
				completion_at = null
			} = req.body;
			let icon = req.file?.filename ? '/pictures/' + req.file?.filename : '/pictures/project-icon-current.png';
			let project = await db.findOne('select * from projects where project_num = ?', [project_num]);
			if (project) {
				return res.send({
					success: false,
					message: '该项目或项目编号已经存在了！'
				});
			}
			const [results] = await db.query(
				'insert into projects (icon, title, project_num, priority, total_matter, finished_matter, create_by, completion_at) values (?,?,?,?,?,?,?,?)',
				[icon, title, project_num, priority, total_matter, finished_matter, req.user.id, completion_at]
			);
			if (results) {
				await db.query('insert into projectNeeds (project_id, manager_id) values (?,?)', [
					results.insertId,
					req.user.id
				]);
			}
			project = await db.findOne('select * from projects where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				project
			});
		}
	)
	/**
	 * 修改项目
	 */
	.put(
		'/update_project/:tid',
		picture.single('icon'),
		Authentication('guest'),
		validate([
			validators.tid,
			validators.project_title,
			validators.project_num,
			validators.project_priority,
			validators.completion_at
		]),
		async (req, res) => {
			const { title, project_num, priority, completion_at } = req.body;
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结，不能修改！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能修改！'
				});
			}
			let icon = req.file?.filename ? '/pictures/' + req.file?.filename : project.icon;
			await db.query(
				'update projects set icon = ?, title = ?, project_num = ?, priority = ?, completion_at = ?, modified_at = CURRENT_TIMESTAMP where id = ?',
				[icon, title, project_num, priority, completion_at, req.params?.tid]
			);
			project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (project) {
				project.person_list = [];
				const [person] = await db.query(`select * from projectNeeds where project_id = ${project.id}`);
				for (let item of person) {
					const manager = await db.findOne(`select id,avatar,nickname from managers where id = ${item.manager_id}`);
					project.person_list.push(manager);
				}
			}
			res.send({
				success: true,
				message: '更新成功',
				project
			});
		}
	)
	/**
	 * 完结/删除项目
	 */
	.put(
		'/finish_delete_project/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.editProjectsType]),
		async (req, res) => {
			const { type = '' } = req.body;
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能修改！'
				});
			}
			let bool, str;
			if (type === 'is_finished') {
				if (project.is_delete) {
					return res.send({
						success: false,
						message: '该项目已被删除，不能修改！'
					});
				}
				bool = project.is_finished ? false : true;
				str = project.is_finished ? '项目开放' : '项目完结';
			} else if (type === 'is_delete') {
				bool = project.is_delete ? false : true;
				str = project.is_delete ? '项目保留' : '项目删除';
			}
			await db.query(`update projects set ${type} = ? where id = ?`, [bool, req.params?.tid]);
			project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (project) {
				project.person_list = [];
				const [person] = await db.query(`select * from projectNeeds where project_id = ${project.id}`);
				for (let item of person) {
					const manager = await db.findOne(`select id,avatar,nickname from managers where id = ${item.manager_id}`);
					project.person_list.push(manager);
				}
			}
			res.send({
				success: true,
				message: `${str}，状态更新成功`,
				project
			});
		}
	)
	/**
	 * 获取项目明细
	 * 获取单个项目、包含项目人员相关等等
	 */
	.get('/get_project_info/:tid', validate([validators.tid]), async (req, res) => {
		const { tid } = req.params;
		const project = await db.findOne(`select * from projects where id = ${tid}`);
		project.person_list = [];
		if (project) {
			const [person] = await db.query(`select * from projectNeeds where project_id = ${project.id}`);
			for (let item of person) {
				const manager = await db.findOne(`select id,avatar,nickname from managers where id = ${item.manager_id}`);
				project.person_list.push(manager);
			}
		}
		res.send({
			success: true,
			project
		});
	})
	/**
	 * 获取所有管理员
	 * 获取所有项目参加人员
	 */
	.get('/get_all_person', async (req, res) => {
		let wheres = ['where status = 0'];
		const [results] = await db.query(`select id,avatar,nickname from managers ${wheres.join(' and ')}`);
		res.send({
			success: true,
			personnelList: results
		});
	})
	/**
	 * 修改项目人员
	 * 添加一个或多个、删除一个或多个、替换一个或多个、混合
	 */
	.put(
		'/update_project_person/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.manager_list]),
		async (req, res) => {
			const { manager_list } = req.body;
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能修改！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			let [originalPersonResult] = await db.query('select * from projectNeeds where project_id = ?', [req.params?.tid]);
			let originalPersonArray = [];
			for (let item of originalPersonResult) {
				originalPersonArray.push(item.manager_id);
			}
			comparePersonArrays(originalPersonArray, manager_list)
				.then(async (result) => {
					const { diffArrayDeletePerson, diffArrayAddPerson } = result;
					if (diffArrayDeletePerson.length === 0 && diffArrayAddPerson.length === 0) {
						return res.send({
							success: true,
							message: '项目人员没有变化',
							state: 'nochange'
						});
					} else if (diffArrayDeletePerson.length === 0 && diffArrayAddPerson.length > 0) {
						for (let manager_id of diffArrayAddPerson) {
							await db.query('insert into projectNeeds (project_id, manager_id) values (?,?)', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '添加成功',
							state: 'add',
							diffArrayAddPerson
						});
					} else if (diffArrayDeletePerson.length > 0 && diffArrayAddPerson.length === 0) {
						for (let manager_id of diffArrayDeletePerson) {
							let exits = await db.findOne('select * from projectNeeds where project_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
							if (exits && manager_id === project.create_by) {
								return res.send({
									success: false,
									message: '项目创建者不能被移除',
									error_manager_id: manager_id
								});
							}
							await db.query('delete from projectNeeds where project_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '删除成功',
							state: 'delete',
							diffArrayDeletePerson
						});
					} else if (diffArrayDeletePerson.length > 0 && diffArrayAddPerson.length > 0) {
						for (let manager_id of diffArrayDeletePerson) {
							let exits = await db.findOne('select * from projectNeeds where project_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
							if (exits && manager_id === project.create_by) {
								return res.send({
									success: false,
									message: '项目创建者不能被移除',
									error_manager_id: manager_id
								});
							}
							await db.query('delete from projectNeeds where project_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
						}
						for (let manager_id of diffArrayAddPerson) {
							await db.query('insert into projectNeeds (project_id, manager_id) values (?,?)', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '修改成功',
							state: 'update',
							diffArrayDeletePerson,
							diffArrayAddPerson
						});
					}
				})
				.catch((errors) => {
					const { success, message: error_manager_id } = errors;
					res.send({
						success,
						message: '项目人员异常或不存在',
						error_manager_id: parseInt(error_manager_id)
					});
				});
		}
	)
	/**
	 * 添加项目人员
	 * 添加一个或多个
	 */
	.post(
		'/add_projectNeed/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.manager_list]),
		async (req, res) => {
			const { manager_list } = req.body;
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能修改！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			for (let manager_id of manager_list) {
				let manager = await db.findOne('select * from managers where id = ?', [manager_id]);
				let exits = await db.findOne('select * from projectNeeds where project_id = ? and manager_id = ?', [
					req.params?.tid,
					manager_id
				]);
				if (!manager || manager.status) {
					return res.send({
						success: false,
						message: '该管理员异常或不存在，请确认后添加',
						index: manager_list.indexOf(manager_id)
					});
				}
				if (exits) {
					return res.send({
						success: false,
						message: '该成员已在项目人员中',
						index: manager_list.indexOf(manager_id)
					});
				}
			}
			for (let manager_id of manager_list) {
				await db.query('insert into projectNeeds (project_id, manager_id) values (?,?)', [req.params?.tid, manager_id]);
			}
			res.send({
				success: true,
				message: '添加成功'
			});
		}
	)
	/**
	 * 删除项目人员
	 * 删除一个或多个
	 */
	.delete(
		'/delete_projectNeed/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.manager_list]),
		async (req, res) => {
			const { manager_list } = req.body;
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能修改！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			for (let manager_id of manager_list) {
				let manager = await db.findOne('select * from managers where id = ?', [manager_id]);
				let exits = await db.findOne('select * from projectNeeds where project_id = ? and manager_id = ?', [
					req.params?.tid,
					manager_id
				]);
				if (exits && manager_id === project.create_by) {
					return res.send({
						success: false,
						message: '项目创建者不能被移除',
						index: manager_list.indexOf(manager_id)
					});
				}
				if (!manager || manager.status) {
					return res.send({
						success: false,
						message: '该管理员异常或不存在，请确认后添加',
						index: manager_list.indexOf(manager_id)
					});
				}
				if (!exits) {
					return res.send({
						success: false,
						message: '该成员不存在项目人员中',
						index: manager_list.indexOf(manager_id)
					});
				}
			}
			for (let manager_id of manager_list) {
				await db.query('delete from projectNeeds where project_id = ? and manager_id = ?', [
					req.params?.tid,
					manager_id
				]);
			}
			res.send({
				success: true,
				message: '移除成功'
			});
		}
	)
	/**
	 * 获取项目事件
	 * 获取单个项目的所有事件
	 */
	.get('/get_product_matter/:tid', validate([validators.tid]), async (req, res) => {
		const { tid } = req.params;
		const project = await db.findOne(
			`select id, priority, is_finished, is_delete, create_by from projects where id = ${tid}`
		);
		project.person_list = [];
		if (project) {
			project.create_by = await db.findOne(
				`select id,avatar,nickname,job from managers where id = ${project.create_by}`
			);
			const [person] = await db.query(`select * from projectNeeds where project_id = ${project.id}`);
			for (let item of person) {
				const manager = await db.findOne(`select id,avatar,nickname from managers where id = ${item.manager_id}`);
				project.person_list.push(manager);
			}
		}
		const { type = '' } = req.query;
		let wheres = [`where project_id = ${tid}`];
		if (type !== '') {
			wheres.push(`type = '${type}'`);
		}
		// group by type
		const [results] = await db.query(
			`select count(*) as count, type from matters ${wheres.join(' and ')} group by type`
		);
		// 从分组中，获取每个类型的事件
		for (let i of results) {
			const [matter] = await db.query(`select * from matters where project_id = ${tid} and type = '${i.type}'`);
			// 为每个事件添加标签、文件和人员
			for (let j of matter) {
				[j.tags] = await db.query(`select * from matterTags where matter_id = ${j.id}`);
				[j.files] = await db.query(`select * from matterFiles where matter_id = ${j.id}`);
				[j.person_list] = await db.query(`select * from matterNeeds where matter_id = ${j.id}`);
			}
			i[`${i.type}`] = matter;
		}
		res.send({
			success: true,
			project,
			matters: results
		});
	})
	/**
	 * 获取所有事件
	 * 获取最近事件
	 */
	.get('/get_all_matter', validate([validators.limit, validators.offset]), async (req, res) => {
		const { limit = '3', offset = '0' } = req.query;
		let matters = await db.select(`select * from matters limit ${limit} offset ${offset}`);
		res.send({
			success: true,
			matters
		});
	})
	/**
	 * 获取单个事件详情
	 * 获取单个事件、包含事件人员标签相关等等
	 */
	.get('/get_matter_info/:tid', validate([validators.tid]), async (req, res) => {
		const { tid } = req.params;
		let matter = await db.findOne(`select * from matters where id = ${tid}`);
		matter.tags = await db.select('select id, tag_name, color, text_color from matterTags where matter_id = ?', [
			matter.id
		]);
		matter.files = await db.select('select * from matterFiles where matter_id = ?', [matter.id]);
		matter.person_list = await db.select('select * from matterNeeds where matter_id = ?', [matter.id]);
		res.send({
			success: true,
			matter
		});
	})
	/**
	 * 添加项目事件
	 */
	.post(
		'/add_project_matter/:tid',
		customUpload.fields([
			{ name: 'icon', maxCount: 1 },
			{ name: 'files', maxCount: 4 }
		]),
		validate([
			validators.tid,
			validators.matter_title,
			validators.matter_description,
			validators.matter_type,
			validators.matter_tagList,
			validators.manager_list
		]),
		async (req, res) => {
			const { title, description, type = 'todo', tag_list, manager_list } = req.body;
			const parseTagList = JSON.parse(tag_list) || [];
			const parseManagerList = JSON.parse(manager_list) || [];
			let project = await db.findOne('select * from projects where id = ?', [req.params?.tid]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (req.user.role !== 'super' || req.user.id !== project.create_by) {
				return res.send({
					success: false,
					message: '非该项目创建人或超级管理员不能添加！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			let { icon, files } = req.files;
			let iconFilename = icon && icon[0]?.filename ? '/pictures/' + icon[0].filename : '';
			const [results] = await db.query(
				'insert into matters (title, description, icon, type, project_id) values (?,?,?,?,?)',
				[title, description, iconFilename, type, req.params?.tid]
			);
			if (results.insertId) {
				if (parseTagList.length > 0) {
					for (let item of parseTagList) {
						let { tag_name, color = '#000', text_color = '#fff' } = item;
						await db.query('insert into matterTags (tag_name, color, text_color, matter_id) values (?,?,?,?)', [
							tag_name,
							color,
							text_color,
							results.insertId
						]);
					}
				}
				if (parseManagerList && parseManagerList.length > 0) {
					for (let manager_id of parseManagerList) {
						let manager = await db.findOne('select * from managers where id = ?', [manager_id]);
						if (!manager || manager.status) {
							return res.send({
								success: false,
								message: '该管理员异常或不存在，请确认后添加',
								index: parseManagerList.indexOf(manager_id)
							});
						}
						await db.query('insert into matterNeeds (matter_id, manager_id) values (?,?)', [
							results.insertId,
							manager_id
						]);
					}
				}
				if (files && files.length > 0) {
					for (let file of files) {
						let { originalname, encoding, mimetype, size, filename } = file;
						await db.query(
							'insert into matterFiles (originalname, encoding, mimetype, size, url, matter_id) values (?,?,?,?,?,?)',
							[originalname, encoding, mimetype, size, '/files/' + filename, results.insertId]
						);
					}
				}
			}
			// 项目事件条数+1
			await db.query('update projects set total_matter = total_matter + 1 where id = ?', [req.params?.tid]);
			if (type === 'done') {
				await db.query('update projects set finished_matter = finished_matter + 1 where id = ?', [req.params?.tid]);
			}
			let matter = await db.findOne('select * from matters where id = ?', [results.insertId]);
			matter.tags = await db.select('select * from matterTags where matter_id = ?', [results.insertId]);
			matter.files = await db.select('select * from matterFiles where matter_id = ?', [results.insertId]);
			matter.person_list = await db.select('select * from matterNeeds where matter_id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				matter
			});
		}
	)
	/**
	 * 修改项目事件
	 */
	.put(
		'/edit_project_matter/:tid',
		customUpload.fields([
			{ name: 'icon', maxCount: 1 },
			{ name: 'files', maxCount: 4 }
		]),
		validate([
			validators.tid,
			validators.matter_title,
			validators.matter_description,
			validators.matter_type,
			validators.matter_tagList
		]),
		async (req, res) => {
			const { icon: oldIcon, title, description, type, tag_list } = req.body;
			const parseTagList = JSON.parse(tag_list) || [];
			let matter = await db.findOne('select * from matters where id = ?', [req.params?.tid]);
			if (!matter) {
				return res.send({
					success: false,
					message: '该事件不存在！'
				});
			}
			if (matter.is_finished) {
				return res.send({
					success: false,
					message: '该事件已完结！'
				});
			}
			let project = await db.findOne('select * from projects where id = ?', [matter.project_id]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			if (project) {
				let person_list = await db.select(`select * from projectNeeds where project_id = ${project.id}`);
				person_list = person_list.map((item) => item.manager_id);
				if (req.user.role !== 'super' || !person_list.includes(req.user.id)) {
					return res.send({
						success: false,
						message: '非该项目参与人或超级管理员不能修改！'
					});
				}
			}

			let { icon, files } = req.files;
			let iconFilename = icon && icon[0]?.filename ? '/pictures/' + icon[0].filename : oldIcon ? matter.icon : '';
			// 修改事件基本信息
			await db.query('update matters set title = ?, description = ?, icon = ?, type = ? where id = ?', [
				title,
				description,
				iconFilename,
				type,
				req.params?.tid
			]);
			// 修改项目条数，先判断原先的type，再判断修改后的type
			if (type === 'done' && matter.type !== 'done') {
				await db.query('update projects set finished_matter = finished_matter + 1 where id = ?', [matter.project_id]);
			} else if (type !== 'done' && matter.type === 'done') {
				await db.query('update projects set finished_matter = finished_matter - 1 where id = ?', [matter.project_id]);
			}
			const oldTags = await db.select('select * from matterTags where matter_id = ?', [req.params?.tid]);
			if (oldTags.length > 0 && parseTagList.length > 0) {
				compareMatterTagArrays(oldTags, parseTagList, req.params?.tid).catch((errors) => {
					const { success, message } = errors;
					return res.send({
						success,
						message
					});
				});
			}
			matter = await db.findOne('select * from matters where id = ?', [req.params?.tid]);
			matter.tags = await db.select('select * from matterTags where matter_id = ?', [req.params?.tid]);
			matter.files = await db.select('select * from matterFiles where matter_id = ?', [req.params?.tid]);
			matter.person_list = await db.select('select * from matterNeeds where matter_id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '修改成功',
				matter
			});
		}
	)
	/**
	 * 修改事件人员
	 * 添加一个或多个、删除一个或多个、替换一个或多个、混合
	 */
	.put(
		'/update_matter_person/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.manager_list]),
		async (req, res) => {
			const { manager_list } = req.body;
			let matter = await db.findOne('select * from matters where id = ?', [req.params?.tid]);
			if (!matter) {
				return res.send({
					success: false,
					message: '该事件不存在！'
				});
			}
			if (matter.is_finished) {
				return res.send({
					success: false,
					message: '该事件已完结！'
				});
			}
			let project = await db.findOne('select * from projects where id = ?', [matter.project_id]);
			if (!project) {
				return res.send({
					success: false,
					message: '该项目不存在！'
				});
			}
			if (project.is_delete || project.is_finished) {
				return res.send({
					success: false,
					message: '该项目被删除或已完结！'
				});
			}
			if (project) {
				let person_list = await db.select(`select * from projectNeeds where project_id = ${project.id}`);
				person_list = person_list.map((item) => item.manager_id);
				if (req.user.role !== 'super' || !person_list.includes(req.user.id)) {
					return res.send({
						success: false,
						message: '非该项目参与人或超级管理员不能修改！'
					});
				}
			}
			let [originalPersonResult] = await db.query('select * from matterNeeds where matter_id = ?', [req.params?.tid]);
			let originalPersonArray = [];
			for (let item of originalPersonResult) {
				originalPersonArray.push(item.manager_id);
			}
			comparePersonArrays(originalPersonArray, manager_list)
				.then(async (result) => {
					const { diffArrayDeletePerson, diffArrayAddPerson } = result;
					if (diffArrayDeletePerson.length === 0 && diffArrayAddPerson.length === 0) {
						return res.send({
							success: true,
							message: '项目人员没有变化',
							state: 'nochange'
						});
					} else if (diffArrayDeletePerson.length === 0 && diffArrayAddPerson.length > 0) {
						for (let manager_id of diffArrayAddPerson) {
							await db.query('insert into matterNeeds (matter_id, manager_id) values (?,?)', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '添加成功',
							state: 'add',
							diffArrayAddPerson
						});
					} else if (diffArrayDeletePerson.length > 0 && diffArrayAddPerson.length === 0) {
						for (let manager_id of diffArrayDeletePerson) {
							await db.query('delete from matterNeeds where matter_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '删除成功',
							state: 'delete',
							diffArrayDeletePerson
						});
					} else if (diffArrayDeletePerson.length > 0 && diffArrayAddPerson.length > 0) {
						for (let manager_id of diffArrayDeletePerson) {
							await db.query('delete from matterNeeds where matter_id = ? and manager_id = ?', [
								req.params?.tid,
								manager_id
							]);
						}
						for (let manager_id of diffArrayAddPerson) {
							await db.query('insert into matterNeeds (matter_id, manager_id) values (?,?)', [
								req.params?.tid,
								manager_id
							]);
						}
						res.send({
							success: true,
							message: '修改成功',
							state: 'update',
							diffArrayDeletePerson,
							diffArrayAddPerson
						});
					}
				})
				.catch((errors) => {
					const { success, message: error_manager_id } = errors;
					res.send({
						success,
						message: '项目人员异常或不存在',
						error_manager_id: parseInt(error_manager_id)
					});
				});
		}
	)
	/**
	 * 删除事件
	 * 删除事件、事件相关人员、事件相关标签、事件相关文件
	 */
	.delete('/delete_matter/:tid', validate([validators.tid]), async (req, res) => {
		let matter = await db.findOne('select * from matters where id = ?', [req.params?.tid]);
		if (!matter) {
			return res.send({
				success: false,
				message: '该事件不存在！'
			});
		}
		if (matter.is_finished) {
			return res.send({
				success: false,
				message: '该事件已完结！'
			});
		}
		let project = await db.findOne('select * from projects where id = ?', [matter.project_id]);
		if (!project) {
			return res.send({
				success: false,
				message: '该项目不存在！'
			});
		}
		if (project.is_delete || project.is_finished) {
			return res.send({
				success: false,
				message: '该项目被删除或已完结！'
			});
		}
		if (project) {
			let person_list = await db.select(`select * from projectNeeds where project_id = ${project.id}`);
			person_list = person_list.map((item) => item.manager_id);
			if (req.user.role !== 'super' || !person_list.includes(req.user.id)) {
				return res.send({
					success: false,
					message: '非该项目参与人或超级管理员不能修改！'
				});
			}
		}
		// 项目条数-1
		await db.query('update projects set total_matter = total_matter - 1 where id = ?', [matter.project_id]);
		if (matter.type === 'done') {
			await db.query('update projects set finished_matter = finished_matter - 1 where id = ?', [matter.project_id]);
		}
		// 删除事件相关人员
		await db.query('delete from matterNeeds where matter_id = ?', [req.params?.tid]);
		// 删除事件相关标签
		await db.query('delete from matterTags where matter_id = ?', [req.params?.tid]);
		// 删除事件相关文件
		await db.query('delete from matterFiles where matter_id = ?', [req.params?.tid]);
		// 删除事件
		await db.query('delete from matters where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: '删除成功'
		});
	})
	/**
	 * 获取所有标签--将废弃
	 */
	.get('/get_all_tag', validate([validators.qTagName, validators.is_delete]), async (req, res) => {
		const { tagName = '', is_delete = '' } = req.query;
		let wheres = ['where id is not null'];
		if (tagName !== '') {
			wheres.push(`tagName like '%${tagName}%'`);
		} else {
			wheres.push(`tagName is not null`);
		}
		if (is_delete !== '') {
			wheres.push(`is_delete = '${is_delete}'`);
		} else {
			wheres.push(`is_delete is not null`);
		}
		const [results] = await db.query(`select * from tags ${wheres.join(' and ')}`);
		res.send({
			success: true,
			tags: results
		});
	})
	/**
	 * 添加标签--将废弃
	 */
	.post('/add_tag', validate([validators.tagName]), async (req, res) => {
		const { tagName } = req.body;
		let tag = await db.findOne('select * from tags where tagName = ?', [tagName]);
		if (tag) {
			return res.send({
				success: false,
				message: '已存在该标签'
			});
		}
		const [results] = await db.query('insert into tags (tagName) values (?)', [tagName]);
		tag = await db.findOne('select * from tags where id = ?', [results.insertId]);
		res.send({
			success: true,
			message: '添加成功',
			tag
		});
	})
	/**
	 * 修改标签--将废弃
	 */
	.put('/update_tag/:tid', validate([validators.tid, validators.tagName]), async (req, res) => {
		const { tagName } = req.body;
		let tag = await db.findOne('select * from tags where id = ?', [req.params?.tid]);
		if (!tag) {
			return res.send({
				success: false,
				message: '该标签不存在'
			});
		}
		if (tag.tagName !== tagName) {
			await db.query('update tags set tagName = ? where id = ?', [tagName, req.params?.tid]);
			tag = await db.findOne('select * from tags where id = ?', [req.params?.tid]);
		}
		res.send({
			success: true,
			message: '更新成功',
			tag
		});
	})
	/**
	 * 删除标签--将废弃
	 */
	.put('/delete_tag/:tid', validate([validators.tid]), async (req, res) => {
		let tag = await db.findOne('select * from tags where id = ?', [req.params?.tid]);
		if (!tag || tag.is_delete) {
			return res.send({
				success: false,
				message: '不存在，或已删除该标签'
			});
		}
		await db.query('update tags set is_delete = ? where id = ?', [1, req.params?.tid]);
		tag = await db.findOne('select * from tags where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: '删除成功',
			tag
		});
	})
	/**
	 * 获取所有分类
	 * 仅仅获取父分类
	 */
	.get('/get_all_category', validate([validators.limit, validators.offset]), async (req, res) => {
		const { name = '', status = '', limit = 10, offser = 0 } = req.query;
		let wheres = ['where if_parent = 1'];
		const conditions = {
			name: name !== '' ? `name like '%${name}%'` : '',
			status: status !== '' ? `status = '${status}'` : ''
		};
		Object.keys(conditions).forEach((key) => {
			if (conditions[key]) {
				wheres.push(conditions[key]);
			}
		});
		const [results] = await db.query(
			`select * from categories ${wheres.join(' and ')} limit ${limit} offset ${offser}`
		);
		// 获取分类下的是否含有子分类
		for (let item of results) {
			const children = await db.findOne(`select * from categories where parent_id = ${item.id}`);
			item.hasChildren = children ? true : false;
		}
		const { count } = await db.findOne(`select count(*) as count from categories ${wheres.join(' and ')}`);
		res.send({
			success: true,
			categorys: results,
			query: {
				count
			}
		});
	})
	/**
	 * 获取父分类以下所有子分类
	 */
	.get('/get_category_info/:tid', validate([validators.tid]), async (req, res) => {
		const { tid } = req.params;
		let exits = await db.findOne('select * from categories where id = ?', [tid]);
		if (!exits) {
			return res.send({
				success: false,
				message: '该分类不存在'
			});
		}
		let children = await db.select('select * from categories where parent_id = ?', [tid]);
		res.send({
			success: true,
			children
		});
	})
	/**
	 * 添加分类
	 */
	.post(
		'/add_category',
		validate([
			validators.category_name,
			validators.category_parentId,
			validators.category_ifParent,
			validators.category_sort
		]),
		async (req, res) => {
			const { name, if_parent, parent_id, sort } = req.body;
			let category = await db.findOne('select * from categories where name = ?', [name]);
			if (category) {
				return res.send({
					success: false,
					message: '已存在该分类'
				});
			}
			// 如果parent_id不为0或者null，判断是否存在该父分类
			if (parent_id) {
				let parent = await db.findOne('select * from categories where id = ?', [parent_id]);
				if (!parent) {
					return res.send({
						success: false,
						message: '父分类不存在'
					});
				}
			}
			const [results] = await db.query('insert into categories (name, if_parent, parent_id, sort) values (?,?,?,?)', [
				name,
				if_parent,
				parent_id,
				sort
			]);
			category = await db.findOne('select * from categories where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				category
			});
		}
	)
	/**
	 * 修改分类
	 */
	.put(
		'/update_category/:tid',
		validate([
			validators.tid,
			validators.category_name,
			validators.category_parentId,
			validators.category_ifParent,
			validators.category_sort
		]),
		async (req, res) => {
			const { name, if_parent, parent_id, sort } = req.body;
			let exist = await db.findOne('select * from categories where name = ?', [name]);
			if (exist && exist.id !== req.params?.tid) {
				return res.send({
					success: false,
					message: '已存在该分类'
				});
			}
			let category = await db.findOne('select * from categories where id = ?', [req.params?.tid]);
			if (!category) {
				return res.send({
					success: false,
					message: '该分类不存在'
				});
			}
			// 如果parent_id不为0或者null，判断是否存在该父分类
			if (parent_id) {
				let parent = await db.findOne('select * from categories where id = ?', [parent_id]);
				if (!parent) {
					return res.send({
						success: false,
						message: '父分类不存在'
					});
				}
			}
			// 如果该分类下有子分类，不能修改是否为父分类
			if (category.if_parent === 1 && if_parent === 0) {
				let children = await db.findOne('select * from categories where parent_id = ?', [req.params?.tid]);
				if (children) {
					return res.send({
						success: false,
						message: '该父分类下有子分类，不能修改为子分类'
					});
				}
			}
			await db.query('update categories set name = ?, if_parent = ?, parent_id = ?, sort = ? where id = ?', [
				name,
				if_parent,
				parent_id,
				sort,
				req.params?.tid
			]);
			category = await db.findOne('select * from categories where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '修改成功',
				category
			});
		}
	)
	/**
	 * 删除分类
	 * 删除父分类判断
	 */
	.delete('/delete_category/:tid', validate([validators.tid]), async (req, res) => {
		let category = await db.findOne('select * from categories where id = ?', [req.params?.tid]);
		if (!category) {
			return res.send({
				success: false,
				message: '该分类不存在'
			});
		}

		if (category.if_parent) {
			let children = await db.select('select id from categories where parent_id = ?', [req.params?.tid]);
			if (children.length > 0) {
				return res.send({
					success: false,
					message: '该父分类下有子分类，不能删除'
				});
			}
		}

		try {
			await db.query('delete from categories where id = ?', [req.params?.tid]);
			return res.send({
				success: true,
				message: '删除成功'
			});
		} catch (e) {
			// 判断e.sqlMessage中是否包含'foreign key constraint fails'，如果包含则说明有外键约束，不能删除
			if (e.sqlMessage.includes('foreign key constraint fails')) {
				return res.send({
					success: false,
					message: '该分类下有关联数据，不能删除'
				});
			} else {
				return res.send({
					success: false,
					message: '删除失败'
				});
			}
		}
	})
	/**
	 * 显示或隐藏分类
	 */
	.put('/edit_category_status/:tid', validate([validators.tid, validators.editTinyintType]), async (req, res) => {
		const { type } = req.query;
		let category = await db.findOne('select * from categories where id = ?', [req.params?.tid]);
		if (!category) {
			return res.send({
				success: false,
				message: '不存在该分类'
			});
		}
		let msg = type == 1 ? '显示' : '隐藏';
		await db.query('update categories set status = ? where id = ?', [type, req.params?.tid]);
		category = await db.findOne('select * from categories where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: `${msg}成功`,
			category
		});
	})
	/**
	 * 获取所有品牌
	 */
	.get('/get_all_brand', validate([validators.limit, validators.offset]), async (req, res) => {
		const {
			brand_name = '',
			website = '',
			country = '',
			founded_year = '',
			status = '',
			is_delete = '',
			limit = 10,
			offset = 0
		} = req.query;
		let wheres = ['where id is not null'];

		// 定义查询条件
		const conditions = {
			brand_name: brand_name !== '' ? `brand_name like '%${brand_name}%'` : '',
			website: website !== '' ? `website like '%${website}%'` : '',
			country: country !== '' ? `country = '${country}'` : '',
			founded_year: founded_year !== '' ? `founded_year = '${founded_year}'` : '',
			status: status !== '' ? `status = '${status}'` : '',
			is_delete: is_delete !== '' ? `is_delete = '${is_delete}'` : ''
		};

		// 将所有条件添加到wheres数组中
		Object.keys(conditions).forEach((key) => {
			// 如果条件不为空，添加到wheres数组中
			if (conditions[key]) {
				wheres.push(conditions[key]);
			}
		});

		const [results] = await db.query(`select * from brands ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		const { count } = await db.findOne(`select count(*) as count from brands ${wheres.join(' and ')}`);
		res.send({
			success: true,
			brands: results,
			query: {
				count
			}
		});
	})
	/**
	 * 获取单个品牌
	 */
	.get('/get_brand_info/:tid', validate([validators.tid]), async (req, res) => {
		const brand = await db.findOne('select * from brands where id = ?', [req.params?.tid]);
		if (!brand) {
			return res.send({
				success: false
			});
		}
		res.send({
			success: true,
			brand
		});
	})
	/**
	 * 添加品牌
	 */
	.post(
		'/add_brand',
		logo.single('logo'),
		validate([
			validators.brand_name,
			validators.brand_description,
			validators.brand_website,
			validators.brand_country,
			validators.brand_founded_year
		]),
		async (req, res) => {
			const { brand_name, description, website_prefix, website, country, founded_year } = req.body;
			let brand = await db.findOne('select * from brands where brand_name = ?', [brand_name]);
			if (brand) {
				return res.send({
					success: false,
					message: '已存在该品牌'
				});
			}
			let logo = req.file?.filename ? '/logos/' + req.file?.filename : '';
			const [results] = await db.query(
				'insert into brands (logo, brand_name, description, website, country, founded_year) values (?,?,?,?,?,?)',
				[logo, brand_name, description, website ? website_prefix + website : '', country, founded_year]
			);
			brand = await db.findOne('select * from brands where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				brand
			});
		}
	)
	/**
	 * 修改品牌
	 */
	.put(
		'/update_brand/:tid',
		logo.single('logo'),
		validate([
			validators.tid,
			validators.brand_name,
			validators.brand_description,
			validators.brand_website,
			validators.brand_country,
			validators.brand_founded_year
		]),
		async (req, res) => {
			const { logo, brand_name, description, website_prefix, website, country, founded_year } = req.body;
			let brand = await db.findOne('select * from brands where id = ?', [req.params?.tid]);
			if (!brand) {
				return res.send({
					success: false,
					message: '品牌不存在'
				});
			}
			let _logo = req.file?.filename ? '/logos/' + req.file?.filename : logo ? brand.logo : '';
			await db.query(
				'update brands set logo = ?, brand_name = ?, description = ?, website = ?, country = ?, founded_year = ?, modified_at = CURRENT_TIMESTAMP where id = ?',
				[
					_logo,
					brand_name,
					description,
					website ? website_prefix + website : '',
					country,
					founded_year,
					req.params?.tid
				]
			);
			brand = await db.findOne('select * from brands where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '更新成功',
				brand
			});
		}
	)
	/**
	 * 删除、下架品牌
	 * 删除、下架一个或多个
	 */
	.put(
		'/edit_brand_status',
		validate([validators.editBrandField, validators.idList, validators.editBrandType]),
		async (req, res) => {
			const { field, id_list, type } = req.query;
			let brand_list = JSON.parse(id_list);
			await db.query(
				`update brands set ${field} = ?, modified_at = CURRENT_TIMESTAMP where id in (${brand_list.join(',')})`,
				[type]
			);
			// field is_delete删除 status下架  id_list 品牌id数组 type 1true 0false
			let msg = field == 'is_delete' ? (type == 1 ? '删除' : '恢复') : type == 1 ? '上架' : '下架';
			res.send({
				success: true,
				message: `${msg}成功`
			});
		}
	)
	/**
	 * 获取所有供应商
	 */
	.get('/get_all_vendor', validate([validators.limit, validators.offset]), async (req, res) => {
		const { name = '', type = 'all', phone = '', is_delete = '', limit = '10', offset = '0' } = req.query;
		let wheres = [`where id is not null`];

		const conditions = {
			name: name !== '' ? `name like '%${name}%'` : '',
			type: type !== '' ? `type = '${type}'` : '',
			phone: phone !== '' ? `phone = '${phone}'` : '',
			is_delete: is_delete !== '' ? `is_delete = '${is_delete}'` : ''
		};

		Object.keys(conditions).forEach((key) => {
			if (conditions[key]) {
				wheres.push(conditions[key]);
			}
		});
		const [results] = await db.query(`select * from vendors ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		const { count } = await db.findOne(`select count(*) as count from vendors ${wheres.join(' and ')}`);
		res.send({
			success: true,
			vendors: results,
			query: {
				count
			}
		});
	})
	/**
	 * 添加供应商
	 */
	.post(
		'/add_vendor',
		validate([validators.vendor_name, validators.vendor_type, validators.vendor_phone]),
		async (req, res) => {
			const { name, type, phone = '' } = req.body;
			let vendor = await db.findOne('select * from vendors where type = ? and phone = ?', [type, phone]);
			if (vendor) {
				return res.send({
					success: false,
					message: '已存在该供应商'
				});
			}
			const [results] = await db.query('insert into vendors (name, type, phone) values (?,?,?)', [name, type, phone]);
			vendor = await db.findOne('select * from vendors where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				vendor
			});
		}
	)
	/**
	 * 修改供应商
	 */
	.put(
		'/update_vendor/:tid',
		validate([validators.tid, validators.vendor_name, validators.vendor_type, validators.vendor_phone]),
		async (req, res) => {
			const { name, type, phone } = req.body;
			let vendor = await db.findOne('select * from vendors where id = ?', [req.params?.tid]);
			if (!vendor) {
				return res.send({
					success: false,
					message: '不存在该供应商'
				});
			}
			await db.query('update vendors set name = ?, type = ?, phone = ? where id = ?', [
				name,
				type,
				phone,
				req.params?.tid
			]);
			vendor = await db.findOne('select * from vendors where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '更新成功',
				vendor
			});
		}
	)
	/**
	 * 删除供应商
	 */
	.put('/delete_vendor/:tid', validate([validators.tid, validators.editTinyintType]), async (req, res) => {
		const { type } = req.query;
		let vendor = await db.findOne('select * from vendors where id = ?', [req.params?.tid]);
		if (!vendor) {
			return res.send({
				success: false,
				message: '不存在该供应商'
			});
		}
		let msg = type == 1 ? '删除' : '恢复';
		await db.query('update vendors set is_delete = ? where id = ?', [type, req.params?.tid]);
		vendor = await db.findOne('select * from vendors where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: `${msg}成功`,
			vendor
		});
	})
	/**
	 * 获取所有消费者
	 */
	.get('/get_all_customer', validate([validators.limit, validators.offset]), async (req, res) => {
		const { username = '', nickname = '', gender = '', status = '', limit = '10', offset = '0' } = req.query;
		let wheres = [`where id is not null`];
		if (username === '') {
			wheres.push('username is not null');
		} else {
			wheres.push(`username = '${username}'`);
		}
		if (nickname !== '') {
			wheres.push(`nickname like '%${nickname}%'`);
		}
		if (gender !== '') {
			wheres.push(`gender = '${gender}'`);
		}
		if (status === '') {
			wheres.push('status is not null');
		} else {
			wheres.push(`status = '${status}'`);
		}
		const [results] = await db.query(`select * from customers ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		res.send({
			success: true,
			customers: results
		});
	})
	/**
	 * 禁用、解禁消费者
	 */
	.put('/ban_customer/:tid', validate([validators.tid]), async (req, res) => {
		let customer = await db.findOne('select * from customers where id = ?', [req.params?.tid]);
		if (!customer) {
			return res.send({
				success: false,
				message: '该消费者不存在'
			});
		}
		await db.query('update customers set status = ? where id = ?', [customer.status ? 0 : 1, req.params?.tid]);
		let msg = customer.status ? '解禁成功' : '禁用成功';
		customer = await db.findOne('select * from customers where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: msg,
			customer
		});
	})
	/**
	 * 获取所有商品
	 */
	.get('/get_all_good', validate([validators.qSort, validators.limit, validators.offset]), async (req, res) => {
		const {
			product_name = '',
			collect_num = '',
			buy_num = '',
			is_delete = '',
			status = '',
			brand_id = '',
			category_id = '',
			vendor_id = '',
			limit = '10',
			offset = '0'
		} = req.query;

		let wheres = [`where id is not null`],
			orders = ['order by'];

		const conditions = {
				product_name: product_name !== '' ? `product_name like '%${product_name}%'` : '',
				is_delete: is_delete !== '' ? `is_delete = '${is_delete}'` : '',
				status: status !== '' ? `status = '${status}'` : '',
				brand_id: brand_id !== '' ? `brand_id = '${brand_id}'` : '',
				category_id: category_id !== '' ? `category_id = '${category_id}'` : '',
				vendor_id: vendor_id !== '' ? `vendor_id = '${vendor_id}'` : ''
			},
			conditions2 = {
				collect_num: collect_num !== '' ? `collect_num ${collect_num}` : '',
				buy_num: buy_num !== '' ? `buy_num ${buy_num}` : ''
			};

		Object.keys(conditions).forEach((key) => {
			if (conditions[key]) {
				wheres.push(conditions[key]);
			}
		});

		// 'asc' 升序 'desc' 降序
		Object.keys(conditions2).forEach((key) => {
			if (conditions2[key]) {
				wheres.push(conditions2[key]);
			}
		});

		const [results] = await db.query(
			`select * from goods ${wheres.join(' and ')} ${
				orders.length > 1 ? orders.join(' ') : ''
			} limit ${limit} offset ${offset}`
		);
		const { count } = await db.findOne(
			`select count(*) as count from goods ${wheres.join(' and ')} ${orders.length > 1 ? orders.join(' ') : ''}`
		);
		const categories = await db.select(
			'select id as value, name as label, status from categories where if_parent = 1 and status = 1'
		);
		// 获取分类下的是否含有子分类
		for (let item of categories) {
			const children = await db.select(
				`select id as value, name as label, status from categories where parent_id = ${item.value} and status = 1`
			);
			if (children.length) item.children = children;
		}
		const vendors = await db.select(`select id as value, name as label, phone from vendors where is_delete = 0`);
		const brands = await db.select(
			'select id as value, brand_name as label, status, is_delete from brands where is_delete = 0 and status = 1'
		);
		res.send({
			success: true,
			goods: results,
			brands,
			categories,
			vendors,
			query: {
				count
			}
		});
	})
	/**
	 * 获取商品详情
	 */
	.get('/get_good_detail/:tid', validate([validators.tid]), async (req, res) => {
		let good = await db.findOne(`select * from goods where id = ?`, [req.params.tid]);
		if (!good) {
			return res.send({
				success: false,
				message: '商品不存在'
			});
		}
		good.category = await db.findOne(`select * from categories where id = ?`, [good.category_id]);
		good.brand = await db.findOne(`select * from brands where id = ?`, [good.brand_id]);
		good.vendor = await db.findOne(`select * from vendors where id = ?`, [good.vendor_id]);
		good.skus = await db.select(`select * from skus where good_id = ?`, [good.id]);
		good.message = '该商品为下架商品！';
		res.send({
			success: true,
			good
		});
	})
	/**
	 * 添加商品
	 */
	.post(
		'/add_good',
		picture.single('image'),
		validate([validators.productName, validators.brandId, validators.categoryId, validators.vendorId]),
		async (req, res) => {
			const { product_name, brand_id, category_id, vendor_id } = req.body;
			let good = await db.findOne(
				'select * from goods where product_name = ? and brand_id = ? and category_id = ? and vendor_id = ?',
				[product_name, brand_id, category_id, vendor_id]
			);
			if (good) {
				return res.send({
					success: false,
					message: '该商品已存在'
				});
			}
			let image = req.file?.filename ? '/pictures/' + req.file?.filename : '';
			const [results] = await db.query(
				'insert into goods (image, product_name,  brand_id, category_id, vendor_id) values (?,?,?,?,?)',
				[image, product_name, brand_id, category_id, vendor_id]
			);
			good = await db.findOne('select * from goods where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '添加成功',
				good
			});
		}
	)
	/**
	 * 修改商品
	 */
	.put(
		'/update_good/:tid',
		picture.single('image'),
		validate([validators.tid, validators.productName, validators.brandId, validators.categoryId, validators.vendorId]),
		async (req, res) => {
			let good = await db.findOne('select * from goods where id = ?', [req.params?.tid]);
			if (!good) {
				return res.send({
					success: false,
					message: '商品不存在或已下架'
				});
			}
			if (good.is_delete) {
				return res.send({
					success: false,
					message: '商品已下架不能修改'
				});
			}
			const {
				image = good.image,
				product_name = good.product_name,
				brand_id = good.brand_id,
				category_id = good.category_id,
				vendor_id = good.vendor_id
			} = req.body;
			let exits = await db.findOne(
				'select * from goods where product_name = ? and brand_id = ? and category_id = ? and vendor_id = ?',
				[product_name, brand_id, category_id, vendor_id]
			);
			if (exits && exits.id !== good.id) {
				return res.send({
					success: false,
					message: '该类型商品已经存在一模一样，请换个字段吧'
				});
			}
			let _image = req.file?.filename ? '/pictures/' + req.file?.filename : image ? good.image : '';
			await db.query(
				'update goods set image = ?, product_name = ?, brand_id = ?, category_id = ?, vendor_id = ?, modified_at = CURRENT_TIMESTAMP where id = ?',
				[_image, product_name, brand_id, category_id, vendor_id, req.params?.tid]
			);
			good = await db.findOne('select * from goods where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '修改成功',
				good
			});
		}
	)
	/**
	 * 删除、下架商品
	 * 删除、下架一个或多个
	 */
	.put(
		'/edit_good_status',
		validate([validators.editBrandField, validators.idList, validators.editBrandType]),
		async (req, res) => {
			const { field, id_list, type } = req.query;
			let brand_list = JSON.parse(id_list);
			await db.query(
				`update goods set ${field} = ?, modified_at = CURRENT_TIMESTAMP where id in (${brand_list.join(',')})`,
				[type]
			);
			// field is_delete删除 status下架  id_list 品牌id数组 type 1true 0false
			let msg = field == 'is_delete' ? (type == 1 ? '删除' : '恢复') : type == 1 ? '上架' : '下架';
			res.send({
				success: true,
				message: `${msg}成功`
			});
		}
	)
	/**
	 * 上架/下架商品--将废弃
	 */
	.put('/delete_good/:tid', validate([validators.tid]), async (req, res) => {
		let good = await db.findOne('select * from goods where id = ?', [req.params?.tid]);
		if (!good) {
			return res.send({
				success: false,
				message: '不存在，或已删除该商品'
			});
		}
		let bool = good.is_delete === 1 ? 0 : 1;
		await db.query('update goods set is_delete = ?, modified_at = CURRENT_TIMESTAMP where id = ?', [
			bool,
			req.params?.tid
		]);
		good = await db.findOne('select * from goods where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: bool ? '下架成功' : '上架成功',
			good
		});
	})
	/**
	 * 获取所有sku
	 */
	.get('/get_all_sku', validate([validators.is_delete, validators.limit, validators.offset]), async (req, res) => {
		const { is_delete = '', limit = '10', offset = '0' } = req.query;
		let wheres = [`where id is not null`];
		if (is_delete === '') {
			wheres.push('is_delete is not null');
		} else {
			wheres.push(`is_delete = '${is_delete}'`);
		}
		const [results] = await db.query(`select * from skus ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		res.send({
			success: true,
			skus: results,
			query: {
				count: results.length
			}
		});
	})
	/**
	 * 获取某个商品的所有sku
	 */
	.get('/get_good_sku/:tid', validate([validators.tid, validators.limit, validators.offset]), async (req, res) => {
		const { limit = '10', offset = '0' } = req.query;
		let good = await db.findOne('select * from goods where id = ?', [req.params.tid]);
		if (!good || good.is_delete) {
			return res.send({
				success: false,
				message: '该商品不存在或已下架'
			});
		}
		const [results] = await db.query(`select * from skus where good_id = ? limit ${limit} offset ${offset}`, [
			req.params.tid
		]);
		res.send({
			success: true,
			good,
			good_skus: results,
			query: {
				count: results.length
			}
		});
	})
	/**
	 * 获取某个sku
	 */
	.get('/get_sku_info/:tid', validate([validators.tid]), async (req, res) => {
		let sku = await db.findOne('select * from skus where id = ?', [req.params?.tid]);
		if (!sku) {
			return res.send({
				success: false,
				message: '该sku不存在'
			});
		}
		res.send({
			success: true,
			sku
		});
	})
	/**
	 * 添加某个商品sku
	 */
	.post(
		'/add_good_sku',
		picture.single('image'),
		validate([validators.goodId, validators.skuName, validators.price, validators.rebated]),
		async (req, res) => {
			const { good_id, sku_name, price, rebated } = req.body;
			let good = await db.findOne('select * from goods where id = ?', [good_id]);
			if (!good || good.is_delete) {
				return res.send({
					success: false,
					message: '该商品不存在或已下架'
				});
			}
			let image = req.file?.filename ? '/pictures/' + req.file?.filename : '';
			const [results] = await db.query(
				'insert into skus (image, good_id, sku_name, price, rebated) values (?,?,?,?,?)',
				[image, good_id, sku_name, price, rebated]
			);
			let sku = await db.findOne('select * from skus where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '新增成功',
				sku
			});
		}
	)
	/**
	 * 修改商品sku
	 */
	.put(
		'/update_sku/:tid',
		picture.single('image'),
		validate([validators.tid, validators.skuName, validators.price, validators.rebated]),
		async (req, res) => {
			const { image, sku_name, price, rebated } = req.body;
			let sku = await db.findOne('select * from skus where id = ?', [req.params?.tid]);
			if (!sku || sku.is_delete) {
				return res.send({
					success: false,
					message: '不存在，或已删除该sku'
				});
			}
			let _image = req.file?.filename ? '/pictures/' + req.file?.filename : image ? sku.image : '';
			await db.query('update skus set image = ?, sku_name = ?, price = ?, rebated = ? where id = ?', [
				_image,
				sku_name,
				price,
				rebated,
				req.params?.tid
			]);
			sku = await db.findOne('select * from skus where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '更新成功',
				sku
			});
		}
	)
	/**
	 * 删除商品sku
	 */
	.put('/delete_sku/:tid', validate([validators.tid]), async (req, res) => {
		let sku = await db.findOne('select * from skus where id = ?', [req.params?.tid]);
		if (!sku) {
			return res.send({
				success: false,
				message: '不存在该sku'
			});
		}
		await db.query('update skus set is_delete = ? where id = ?', [sku.is_delete ? 0 : 1, req.params?.tid]);
		let msg = sku.is_delete ? '恢复成功' : '删除成功';
		sku = await db.findOne('select * from skus where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: msg,
			sku
		});
	})
	/**
	 * 获取所有库存管理
	 */
	.get('/get_all_stock', validate([validators.handlerBy, validators.limit, validators.offset]), async (req, res) => {
		const { handler_by = '', limit = '10', offset = '0' } = req.query;
		let wheres = [`where id is not null`];
		if (handler_by === '') {
			wheres.push('handler_by is not null');
		} else {
			wheres.push(`handler_by = '${handler_by}'`);
		}
		const [results] = await db.query(
			`select * from stockManagement ${wheres.join(' and ')} limit ${limit} offset ${offset}`
		);
		res.send({
			success: true,
			stocks: results
		});
	})
	/**
	 * 获取某个sku库存
	 */
	.get('/get_sku_stock/:tid', validate([validators.tid, validators.limit, validators.offset]), async (req, res) => {
		const { limit = '10', offset = '0' } = req.query;
		let stock = await db.findOne('select * from skus where id = ?', [req.params.tid]);
		if (!stock) {
			return res.send({
				success: false,
				message: '该sku库存不存在'
			});
		}
		const [results] = await db.query(`select * from stockManagement where sku_id = ? limit ${limit} offset ${offset}`, [
			req.params?.tid
		]);
		res.send({
			success: true,
			sku_stock: results
		});
	})
	/**
	 * 添加sku库存
	 */
	.post(
		'/add_sku_stock',
		validate([validators.numType, validators.quantity, validators.dateOn, validators.skuId]),
		async (req, res) => {
			const { type = 1, quantity, date_on, sku_id } = req.body;
			let sku = await db.findOne('select * from skus where id = ?', [sku_id]);
			if (!sku || sku.is_delete) {
				return res.send({
					success: false,
					message: '该sku不存在或已删除'
				});
			} else {
				let num;
				if (type !== 0) {
					num = sku.stock + quantity;
				} else {
					num = sku.stock - quantity;
				}
				await db.query('update skus set stock = ? where id = ?', [num, sku.id]);
			}
			const [results] = await db.query(
				'insert into stockManagement (type, quantity, date_on, handler_by, sku_id) values (?,?,?,?,?)',
				[type, quantity, date_on, req.user.id, sku_id]
			);
			let stock = await db.findOne('select * from stockManagement where id = ?', [results.insertId]);
			res.send({
				success: true,
				message: '新增成功',
				stock
			});
		}
	)
	/**
	 * 修改sku库存
	 */
	.put(
		'/update_stock/:tid',
		validate([validators.tid, validators.numType, validators.quantity, validators.dateOn, validators.skuId]),
		async (req, res) => {
			const { type = 1, quantity, date_on, sku_id } = req.body;
			let stock = await db.findOne('select * from stockManagement where id = ?', [req.params?.tid]);
			let sku = await db.findOne('select * from skus where id = ?', [sku_id]);
			if (!sku || sku.is_delete) {
				return res.send({
					success: false,
					message: '该sku不存在或已删除'
				});
			}
			if (!stock || stock.is_delete) {
				return res.send({
					success: false,
					message: '该sku库存不存在或已删除'
				});
			} else {
				let num; // 差值
				if (type != stock.type) {
					// 代码简化
					num = type === 0 ? -quantity - stock.quantity : quantity + stock.quantity;
				} else {
					num = type === 1 ? quantity - stock.quantity : -quantity + stock.quantity;
				}
				// if (type != stock.type) {
				//   if (quantity === stock.quantity) {
				//     if (type === 0) {
				//       num = -2 * quantity
				//     } else {
				//       num = 2 * quantity
				//     }
				//   } else {
				//     if (type === 0) {
				//       num = -quantity - stock.quantity
				//     } else {
				//       num = quantity + stock.quantity
				//     }
				//   }
				// } else {
				//   if (type === 1) {
				//     num = quantity - stock.quantity
				//   } else {
				//     num = stock.quantity - quantity
				//   }
				// }
				await db.query('update skus set stock = ? where id = ?', [sku.stock + num, sku.id]);
			}
			await db.query(
				'update stockManagement set type = ?, quantity = ?, date_on = ?, handler_by = ?, sku_id = ? where id = ?',
				[type, quantity, date_on, req.user.id, sku_id, req.params?.tid]
			);
			stock = await db.findOne('select * from stockManagement where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '更新成功',
				stock
			});
		}
	)
	/**
	 * 百度api测试
	 */
	.post('/baidu_qianfan', async (req, res) => {
		const { prompt } = req.body;
		await getBaiduToken('client_credentials', 'MimkB4op4DuED7WgvHKD3EVi', 'c19WqFRUfPNhY2tlM3hRA5t8QnDFcGTM')
			.then((res) => {
				console.log(res);
			})
			.catch((error) => {
				console.log(error);
			});
		res.send();
	})
	/**
	 * 百度开放平台api,OCR身份证识别
	 */
	.post('/baidu_ocr_idcard', async (req, res) => {
		const { base64, type } = req.body;
		await getBaiduToken('client_credentials', 'dcYBLvDhG5Zq05D77UkgVRoj', 'TCWUgyuhdwSNcrmk6gkyF7plreu4kAeg')
			.then((token) => {
				let params = new FormData();
				params.append('image', base64); //转Base64后的编码
				params.append('id_card_side', type); //身份证正反面参数，正：front，反：back
				params.append('access_token', token);
				baiduIdcard(params)
					.then((data) => {
						res.send(data);
					})
					.catch((error) => {
						res.send(error);
					});
			})
			.catch((error) => {
				res.send(error);
			});
		// res.send({
		//   success: true,
		//   message: '新增成功'
		// })
	})
	// 获取身份证列表 / 某个身份证
	.get('/get_idcard', validate([validators.limit, validators.offset]), async (req, res) => {
		const { realname = '', card_num = '', type = '', limit = '10', offset = '0' } = req.query;
		// 模糊查询
		let wheres = [`where realname like "%${realname}%" and card_num like "%${card_num}%"`];
		if (type !== '') {
			wheres.push(`type = ${type}`);
		}
		const [card_list] = await db.query(`select * from idCards ${wheres.join(' and ')} limit ${limit} offset ${offset}`);
		res.send({
			success: true,
			card_list
		});
	})
	// 审核身份证信息
	.put(
		'/audit_idCard/:tid',
		Authentication('guest'),
		validate([validators.tid, validators.idcardType]),
		async (req, res) => {
			const { type } = req.query;
			let card = await db.findOne('select * from idCards where id = ?', [req.params?.tid]);
			if (!card) return res.send({ success: false, message: '该条数据不存在！' });
			if (card.type == 0 || card.type == 1) return res.send({ success: false, message: '该条数据已经审核过了！' });
			await db.query('update idCards set type = ? where id = ?', [
				parseInt(type) !== NaN ? parseInt(type) : card.type,
				req.params?.tid
			]);
			card = await db.findOne('select * from idCards where id = ?', [req.params?.tid]);
			res.send({ success: true, message: '操作成功！', card });
		}
	)
	// 获取银行卡种类
	.get('/get_bankcard', validate([validators.limit, validators.offset]), async (req, res) => {
		// 分页 搜索 筛选
		const { bank_name = '', bank_card_type = '', limit = 10, offset = 0 } = req.query;
		let wheres = [`where bank_name like "%${bank_name}%"`];
		if (bank_card_type == '') {
			wheres.push('bank_card_type is not null');
		} else {
			wheres.push(`bank_card_type = ${bank_card_type}`);
		}
		let sql = `select * from bankcard_manage ${wheres.join(' and ')} limit ${offset}, ${limit}`;
		const [cards] = await db.query(sql);
		res.send({ success: true, cards });
	})
	// 添加银行卡种类
	.post(
		'/add_bankcard',
		Authentication('guest', 'admin'),
		bankcard.single('pic'),
		validate([validators.bankCardName, validators.bankCardType]),
		async (req, res) => {
			const { bank_name, bank_card_type } = req.body;
			let card = await db.findOne('select * from bankcard_manage where bank_name = ? and bank_card_type = ?', [
				bank_name,
				bank_card_type
			]);
			if (card) return res.send({ success: false, message: '该卡已经存在了！' });
			const [result] = await db.query('insert into bankcard_manage (pic, bank_name, bank_card_type) values (?,?,?)', [
				req.file?.filename,
				bank_name,
				bank_card_type
			]);
			card = await db.findOne('select * from bankcard_manage where id = ?', [result.insertId]);
			res.send({
				success: true,
				message: '添加成功！',
				card
			});
		}
	)
	// 修改银行卡种类信息
	.put(
		'/update_bankcard/:tid',
		Authentication('guest', 'admin'),
		bankcard.single('pic'),
		validate([validators.tid, validators.bankCardName, validators.bankCardType]),
		async (req, res) => {
			const { bank_name, bank_card_type } = req.body;
			let exits = await db.findOne('select * from bankcard_manage where bank_name = ? and bank_card_type = ?', [
				bank_name,
				bank_card_type
			]);
			if (exits && exits.id !== req.params?.tid) return res.send({ success: false, message: '该卡种已经存在了！' });
			let card = await db.findOne('select * from bankcard_manage where id = ?', [req.params?.tid]),
				pic = req.file?.filename || card.pic;
			if (!card) return res.send({ success: false, message: '卡种信息查询失败不存在！' });
			await db.query('update bankcard_manage set pic = ?, bank_name = ?, bank_card_type = ? where id = ?', [
				pic,
				bank_name,
				bank_card_type,
				req.params?.tid
			]);
			card = await db.findOne('select * from bankcard_manage where id = ?', [req.params?.tid]);
			res.send({
				success: true,
				message: '修改成功！',
				card
			});
		}
	)
	// 删除银行卡种类
	.delete('/delete_bankcard/:tid', Authentication('guest', 'admin'), validate([validators.tid]), async (req, res) => {
		const exist = await db.findOne('select * from bankcard_manage where id = ?', [req.params?.tid]);
		if (!exist) return res.send({ success: false, message: '该卡种不存在！' });
		await db.query('delete from bankcard_manage where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: '删除成功！'
		});
	})
	// 获取用户上传银行卡
	.get(
		'/get_user_bankcard',
		Authentication('guest'),
		validate([validators.limit, validators.offset]),
		async (req, res) => {
			const { limit = 10, offset = 0, customer_id, bank_card_number, card_id, bank_card_type, type } = req.query;
			let wheres = ['where id is not null'];
			if (customer_id) {
				wheres.push(`customer_id = ${customer_id}`);
			}
			if (bank_card_number) {
				wheres.push(`bank_card_number like "%${bank_card_number}%"`);
			}
			if (card_id) {
				wheres.push(`card_id = ${card_id}`);
			}
			if (bank_card_type) {
				wheres.push(`bank_card_type = ${bank_card_type}`);
			}
			if (type) {
				wheres.push(`type = ${type}`);
			}
			wheres = wheres.join(' and ');
			const [cards] = await db.query(`select * from bankcards ${wheres} limit ${offset},${limit}`, [req.params?.tid]);
			res.send({
				success: true,
				cards
			});
		}
	)
	// 审核用户上传银行卡
	.put('/audit_bankcard/:tid', Authentication('guest'), validate([validators.tid]), async (req, res) => {
		const { type, mark } = req.body;
		let card = await db.findOne('select * from bankcards where id = ?', [req.params?.tid]);
		if (!card) return res.send({ success: false, message: '银行卡信息不存在！' });
		if (type == 0 && !mark) return res.send({ success: false, message: '不通过标注不能为空！' });
		if (type === card.type && mark === card.mark) return res.send({ success: false, message: '已经审核过了！' });
		await db.query('update bankcards set type = ?,mark = ? where id = ?', [type, mark, req.params?.tid]);
		card = await db.findOne('select * from bankcards where id = ?', [req.params?.tid]);
		res.send({
			success: true,
			message: '审核成功！',
			card
		});
	})
	/**
	 * 获取用户待办列表
	 */
	.get('/get_user_todos', validate([validators.limit, validators.offset]), async (req, res) => {
		const { days = 'all', priority = 'all', urgent = 'all', isFinished = 'all', limit = 10, offset = 0 } = req.query;
		const wheres = ['manager_id = ?'],
			params = [req.user.id];
		if (days != 'all') {
			wheres.push(`created_at between date_add(now(), interval - ${days} day) and now()`);
		}
		if (priority != 'all') {
			wheres.push('priority = ?');
			params.push(priority);
		}
		if (urgent != 'all') {
			wheres.push('urgent = ?');
			params.push(urgent);
		}
		if (isFinished != 'all') {
			wheres.push(`finished_at is ${isFinished == 'true' ? 'not' : ''} null`);
		}
		// 获取查询的总数
		let sql = `select count(*) as count from todos where ${wheres.join(' and ')}`;
		const { count } = await db.findOne(sql, params);
		// 执行查询 sql
		sql = `select * from todos where ${wheres.join(' and ')} order by created_at desc limit ${offset}, ${limit}`;
		const [results] = await db.query(sql, params);
		for (const todo of results) {
			todo.finished_at =
				dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
					? todo.finished_at
					: dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss');
			todo.created_at = dataFormat(todo.created_at, 'YYYY-MM-DD kk:mm:ss');
		}
		res.send({
			success: true,
			query: {
				count,
				limit,
				offset,
				page: Math.ceil(count / limit)
			},
			todos: results
		});
	})
	/**
	 * 新建待办
	 */
	.post(
		'/add_todo',
		validate([validators.title, validators.description, validators.priority, validators.urgent]),
		async (req, res) => {
			const { title, description, priority, urgent } = req.body;
			// 插入新待办
			const [results] = await db.query(
				'insert into todos (title, description, priority, urgent, manager_id) values (?, ?, ?, ?, ?)',
				[title, description, priority, urgent, req.user.id]
			);
			// 查询刚刚插入的新待办信息
			const todo = await db.findOne('select * from todos where id = ?', [results.insertId]);
			todo.finished_at =
				dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
					? todo.finished_at
					: dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss');
			todo.created_at = dataFormat(todo.created_at, 'YYYY-MM-DD kk:mm:ss');
			res.send({
				success: true,
				message: '新建待办成功！',
				todo
			});
		}
	)
	/**
	 * 修改待办信息
	 */
	.put(
		'/edit_todo/:tid',
		validate([validators.title, validators.description, validators.priority, validators.urgent, validators.tid]),
		async (req, res) => {
			let todo = await db.findOne('select * from todos where id = ? and manager_id = ?', [req.params.tid, req.user.id]);
			if (!todo) return res.send({ success: false, message: '该待办信息不存在！' });
			if (todo.finished_at) return res.send({ success: false, message: '该待办已经完成，不能修改！' });
			const { title, description, priority, urgent } = req.body;
			await db.query('update todos set title = ?, description = ?, priority = ?, urgent = ? where id = ?', [
				title,
				description,
				priority,
				urgent,
				req.params.tid
			]);
			todo = await db.findOne('select * from todos where id = ?', [req.params.tid]);
			todo.finished_at =
				dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
					? todo.finished_at
					: dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss');
			todo.created_at = dataFormat(todo.created_at, 'YYYY-MM-DD kk:mm:ss');
			res.send({ success: true, message: '待办修改成功！', todo });
		}
	)
	/**
	 * 删除待办
	 */
	.delete('/delete_todo/:tid', validate([validators.tid]), async (req, res) => {
		const todo = await db.findOne('select * from todos where id = ? and manager_id = ?', [req.params.tid, req.user.id]);
		if (!todo) return res.send({ success: false, message: '该待办信息不存在！' });
		await db.query('delete from todos where id = ?', [req.params.tid]);
		res.send({ success: true, message: '删除待办成功' });
	})
	/**
	 * 完成待办
	 */
	.put('/finish_todo/:tid', validate([validators.tid]), async (req, res) => {
		let todo = await db.findOne('select * from todos where id = ? and manager_id = ?', [req.params.tid, req.user.id]);
		if (!todo) return res.send({ success: false, message: '该待办信息不存在！' });
		if (todo.finished_at) return res.send({ success: false, message: '该待办已经完成，不能修改！' });
		await db.query('update todos set finished_at = current_timestamp where id = ?', [req.params.tid]);
		todo = await db.findOne('select * from todos where id = ?', [req.params.tid]);
		todo.finished_at =
			dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss') === 'Invalid date'
				? todo.finished_at
				: dataFormat(todo.finished_at, 'YYYY-MM-DD kk:mm:ss');
		todo.created_at = dataFormat(todo.created_at, 'YYYY-MM-DD kk:mm:ss');
		res.send({ success: true, message: '恭喜你，又完成一条任务！', todo });
	})
	/**
	 * 统计待办完成情况
	 */
	.get('/stat_todo', async (req, res) => {
		const { id } = req.user,
			{ count } = await db.findOne('select count(*) as count from todos where manager_id = ?', [id]),
			[finished] = await db.query(
				'select priority, urgent, count(*) as count from todos where manager_id = ? and finished_at is not null group by priority, urgent',
				[id]
			),
			[notFinished] = await db.query(
				'select priority, urgent, count(*) as count from todos where manager_id = ? and finished_at is null group by priority, urgent',
				[id]
			);
		res.send({
			success: true,
			query: {
				count
			},
			finished,
			notFinished
		});
	});

const Router = (app) => {
	app.use('/manager', notAuthRouter);
	app.use('/manager', router);
};

module.exports = { Router };
